﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using DapperExtensions;
using System.Data;
using System.Data.SqlClient;
using Models;

namespace DAL.Unit
{
    /// <summary>
    /// 单位管理
    /// </summary>
    public class UnitDAL
    {
        private SqlDbHelper sqldbhelper = new SqlDbHelper();
        #region 新增及更新单位信息
        /// <summary>
        /// 新增及更新单位信息
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public async Task<int> SaveUnit(UnitModel entity)
        {
            int returnVal = 0;
            try
            {
                using (IDbConnection connect = new SqlConnection(sqldbhelper.strCon))
                {
                    string sql = string.Empty;
                    //新增
                    if (string.IsNullOrEmpty(entity.UnitId))
                    {
                        entity.UnitId = Guid.NewGuid().ToString();
                        sql = @"insert into Unit(UnitId,UnitName,UnitCode,SequenceCode,ParentId,ParentSeqCode,UnitType,Province,City,Remark,SortCode) 
                             values(@UnitId,@UnitName,@UnitCode,@SequenceCode,@ParentId,@ParentSeqCode,@UnitType,@Province,@City,@Remark,@SortCode)";
                    }
                    else //更新
                    {
                        sql = @"update Unit set UnitName=@UnitName,UnitCode=@UnitCode,SequenceCode=@SequenceCode,ParentId=@ParentId,ParentSeqCode=@ParentSeqCode,
                          UnitType=@UnitType,Province=@Province,City=@City,Remark=@Remark,SortCode=@SortCode where UnitId=@UnitId";
                    }
                    returnVal = await connect.ExecuteAsync(sql, entity);
                }
            }
            catch (Exception ex)
            {
                return -1;
            }
            return returnVal;
        }
        #endregion

        #region 删除单位信息
        /// <summary>
        /// 删除单位信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<int> RemoveUnit(string id)
        {
            int returnVal = 0;
            try
            {
                using (IDbConnection connect = new SqlConnection(sqldbhelper.strCon))
                {
                    string sql = @"delete Unit  where  UnitId=@UnitId";
                    returnVal = await connect.ExecuteAsync(sql, new { UnitId = id });
                }
            }
            catch (Exception)
            {
                return -1;
            }
            return returnVal;
        }
        #endregion

        #region 获取单位对象
        /// <summary>
        /// 获取单位对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<UnitModel> GetEntity(string id)
        {
            using (IDbConnection connection = new SqlConnection(sqldbhelper.strCon))
            {
                var result = await connection.QueryAsync<UnitModel>(@"select  unitid,unitname,unitcode,sequencecode,parentid,parentseqcode,unittype,province,city,sortcode,
                remark from Unit where UnitId=@UnitId", new { UnitId = id });
                return result.FirstOrDefault();
            }
        }
        #endregion

        #region 获取随机验证码的集合
        /// <summary>
        /// 获取随机验证码的集合
        /// </summary>
        /// <param name="seqCode"></param>
        /// <returns></returns>
        public async Task<List<UnitModel>> GetListBySeqCode(string seqCode)
        {
            List<UnitModel> list = new List<UnitModel>();
            try
            {
                string sql = @"select unitid,unitname,unitcode,sequencecode,parentid,parentseqcode,unittype,province,city,sortcode,
                remark from Unit where sequencecode = @SequenceCode  order by unitcode ";
                using (IDbConnection connection = new SqlConnection(sqldbhelper.strCon))
                {
                    var result = await connection.QueryAsync<UnitModel>(sql, new { SequenceCode = seqCode });
                    list = result.ToList();
                }
            }
            catch (Exception)
            {
                throw;
            }
            return list;
        }
        #endregion


        #region 获取随机验证码的对象
        /// <summary>
        /// 获取随机验证码的对象
        /// </summary>
        /// <param name="seqCode"></param>
        /// <returns></returns>
        public UnitModel GetEntityBySeqCode(string seqCode)
        {
            using (IDbConnection connection = new SqlConnection(sqldbhelper.strCon))
            {
                var result = connection.Query<UnitModel>(@"select  unitid,unitname,unitcode,sequencecode,parentid,parentseqcode,unittype,province,city,sortcode,
                remark from Unit where sequencecode=@SequenceCode", new { SequenceCode = seqCode });
                return result.FirstOrDefault();
            }
        }
        #endregion


        #region 获取当前节点下的子集合

        /// <summary>
        /// 获取当前节点下的子集合
        /// </summary>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public async Task<List<UnitModel>> GetListByParentId(string parentId)
        {
            List<UnitModel> list = new List<UnitModel>();
            try
            {
                string sql = @"select unitid,unitname,unitcode,sequencecode,parentid,parentseqcode,unittype,province,city,sortcode,
                remark from Unit where parentid = @ParentId  order by unitcode ";
                using (IDbConnection connection = new SqlConnection(sqldbhelper.strCon))
                {
                    var result = await connection.QueryAsync<UnitModel>(sql, new { ParentId = parentId });
                    list = result.ToList();
                }
            }
            catch (Exception)
            {
                throw;
            }
            return list;
        }
        #endregion


        #region 获取所有单位信息
        /// <summary>
        /// 获取所有单位信息
        /// </summary>
        /// <returns></returns>
        public async Task<List<UnitModel>> GetList()
        {
            List<UnitModel> list = new List<UnitModel>();
            try
            {
                string sql = "select unitid,unitname,unitcode,sequencecode,parentid,parentseqcode,unittype,province,city,sortcode,remark from Unit";
                using (IDbConnection connection = new SqlConnection(sqldbhelper.strCon))
                {
                    var result = await connection.QueryAsync<UnitModel>(sql);
                    list = result.ToList();
                }
            }
            catch (Exception)
            {
                throw;
            }
            return list;
        }
        #endregion

        #region 获取相同单位下的单位信息
        /// <summary>
        /// 获取相同单位下的单位信息
        /// </summary>
        /// <returns></returns>
        public async Task<List<UnitModel>> GetListByUnitName(string unitName)
        {
            List<UnitModel> list = new List<UnitModel>();
            try
            {
                string sql = @"select unitid,unitname,unitcode,sequencecode,parentid,parentseqcode,unittype,
                   province,city,sortcode,remark from Unit  where unitname = @UnitName";
                using (IDbConnection connection = new SqlConnection(sqldbhelper.strCon))
                {
                    var result = await connection.QueryAsync<UnitModel>(sql, new { UnitName = unitName });
                    list = result.ToList();
                }
            }
            catch (Exception)
            {
                throw;
            }
            return list;
        }
        #endregion
    }
}
